<?php
//Bussiness tire class  for product catalog information 
class Catalog {
	//Retrive all Departments 
	public static function GetDepartments(){
		//Build sql query 
		$sql='select * from department ';
		
		//Execute the query and return result
		return DatabaseHandler::GetAll($sql);
	} 
	
	//Retrive complete deatils for the specified department
	public static function GetDepartmentDetails($departmentId){
		//Build sql query 
		$sql ="select * from department where department_id =:department_id ";
		
		//Build parameters array 
		$params =array(':department_id'=>$departmentId);
		
		//Execute the query and return the results 
		return DatabaseHandler::GetRow($sql,$params);
	} 
	
	//Retrive complete deatils for the specified Category
	public static function GetCategoryDetails($categoryId){
		//Build sql query 
		$sql ="select * from category where category_id =:category_id ";
		
		//Build parameters array 
		$params =array(':category_id'=>$categoryId);
		
		//Execute the query and return the results 
		return DatabaseHandler::GetRow($sql,$params);
	}  
	
	//Retrive categories that belong to  a department 
	public static function GetCategoriesInDepartment($departmentId){
		//Build sql query 
		$sql ="select * from category where department_id =:department_id ";
		
		//Build parameters array 
		$params =array(':department_id'=>$departmentId);
		
		//Execute the query and return the results 
		return DatabaseHandler::GetAll($sql,$params);
	}
	
	// Claculate how many pages of products could be filled by the number of products 
	public static function HowManyPages($countSql,$countSqlParams)
	{   
		//Create Hash for the sql query 
		$queryHashCode = md5($countSql .var_export($countSqlParams,true));
		
		//Verify if we have the query  results in cache 
		if(isset($_SESSION['last_count_hash']) && 
		isset($_SESSION['how_many_pages']) &&
		$_SESSION['last_count_hash'] === $queryHashCode)
		{
			//Retrive the cached value 
			$how_many_pages =$_SESSION['how_many_pages'];	
		}else{

			//Execute the query 
			$items_count = DatabaseHandler::GetOne($countSql,$countSqlParams);
			
			//DatabaseHandler::Close();
			
			//Calculate the number of pages
			$how_many_pages =ceil($items_count/PRODUCTS_PER_PAGE);
			
			//Save the query and its count result in the session 
			$_SESSION['last_count_hash'] =$queryHashCode;
			$_SESSION['how_many_pages']  =$how_many_pages;
			
	    }
	    //Return the number of pages 
	    return $how_many_pages;
	}
	
	//List of products thst belong to category 
	public static function GetProductsInCategory($categoryId,$pageNo,&$HowManyPages){
		//Query that returns number of products in the category 
		$sql1 =" SELECT count(*) 
                FROM product p
                INNER JOIN product_category pc ON p.product_id = pc.product_id
                WHERE category_id =:category_id ";
		//print_r($sql);
		$params1= array(':category_id'=>$categoryId);
		
		//calculate  the number of pages to display the products 
		$HowManyPages = Catalog::HowManyPages($sql1, $params1);
		
		// Calculate the start item 
		print_r($HowManyPages);
		echo " ..........From Category............";
		$start_item =($pageNo -1) * PRODUCTS_PER_PAGE ;
		$pro =PRODUCTS_PER_PAGE;
		$pro1 =(int)$pro;
		
		//Retrive the list of products 
        $test ='....';
		$sql = " SELECT p.product_id,p.name,
		         IF(LENGTH(p.description) <= :short_product_description_length,
		         p.description ,CONCAT(LEFT(p.description ,:short_product_description_length),
		         :dots )) AS description ,p.price,p.discounted_price,
		         p.thumbnail FROM  product p 
		         INNER JOIN product_category pc 
		         ON p.product_id =pc.product_id 
		         WHERE pc.category_id =:category_id 
		         ORDER BY p.display DESC 
		         LIMIT :start_item, :products_per_page ";
		
		
		
		
	 $params = array (
      ':short_product_description_length' =>array(SHORT_PRODUCT_DESCRIPTION_LENGTH,PDO::PARAM_INT),
      ':dots'=>array($test,PDO::PARAM_STR),
      ':category_id' => array($categoryId,PDO::PARAM_INT),
      ':start_item' =>array($start_item,PDO::PARAM_INT),
      ':products_per_page' => array(PRODUCTS_PER_PAGE,PDO::PARAM_INT)
      );
		
		
		
		//Execute the query and return the results 
		return DatabaseHandler::GetAllBind($sql,$params);
		
	}
	
	public static function GetProductsInDepartment($DepartmentId,$pageNo,&$HowManyPages){
		//Query that returns number of products in the Department
		$sql1 ="SELECT count(*) 
                FROM product p
                INNER JOIN product_category pc ON p.product_id = pc.product_id   
                INNER JOIN category c ON pc.category_id = c.category_id
                WHERE (p.display =2 OR p.display =3)
                AND c.department_id = :department_id ";               
                
		//print_r($sql);
		$params1= array(':department_id'=>$DepartmentId);
		
		//calculate  the number of pages to display the products 
		$HowManyPages = Catalog::HowManyPages($sql1, $params1);
		
		print_r($HowManyPages);
		echo " .........From Department.............";
		
		// Calculate the start item 
		
		$start_item =(int)($pageNo -1)* PRODUCTS_PER_PAGE ;
		
		
		//Retrive the list of products 
        $test ='....';
		$sql = " SELECT p.product_id,p.name,
		         IF(LENGTH(p.description) <= :short_product_description_length,
		         p.description ,CONCAT(LEFT(p.description ,:short_product_description_length),
		         :dots )) AS description ,p.price,p.discounted_price,
		         p.thumbnail FROM  product p 
		         INNER JOIN product_category pc ON p.product_id =pc.product_id 
		         INNER JOIN category c ON c.category_id = pc.category_id
		         WHERE (p.display = 2 OR p.display =3)
		         AND c.department_id =:department_id
		         ORDER BY p.display DESC 
		         LIMIT :start_item, :products_per_page ";
		
		
		
		
	 $params = array (
      ':short_product_description_length' =>array(SHORT_PRODUCT_DESCRIPTION_LENGTH,PDO::PARAM_INT),
      ':dots'=>array($test,PDO::PARAM_STR),
      ':department_id' => array($DepartmentId,PDO::PARAM_INT),
      ':start_item' =>array($start_item,PDO::PARAM_INT),
      ':products_per_page' => array(PRODUCTS_PER_PAGE,PDO::PARAM_INT)
      );
		
		
		
		//Execute the query and return the results 
		return DatabaseHandler::GetAllBind($sql,$params);
		
	}
	
	public static function GetProductsOnCatalog($pageNo,&$HowManyPages){
		
		//Return the no of products on catelog page 
		$sql= "SELECT count(*) 
		      FROM product 
		      WHERE display=1 OR display =3";
		
		// calculate the no of pages required  to dispaly the products 
		$HowManyPages =Catalog::HowManyPages($sql, null);
		
		// Calculate the start item 
		$start_item=($pageNo -1)* PRODUCTS_PER_PAGE ;
		
		//Retrive list of products 
		$test ='....';
		$sql = " SELECT product_id,name,
		         IF(LENGTH(description) <= :short_product_description_length,
		         description ,CONCAT(LEFT(description ,:short_product_description_length),
		         :dots )) AS description ,price,discounted_price,
		         thumbnail FROM  product  
		         WHERE display = 1 OR display =3
		         ORDER BY display DESC 
		         LIMIT :start_item, :products_per_page ";
		
		$params = array (
      ':short_product_description_length' =>array(SHORT_PRODUCT_DESCRIPTION_LENGTH,PDO::PARAM_INT),
      ':dots'=>array($test,PDO::PARAM_STR),
      ':start_item' =>array($start_item,PDO::PARAM_INT),
      ':products_per_page' => array(PRODUCTS_PER_PAGE,PDO::PARAM_INT)
      );
		
		
		
		//Execute the query and return the results 
		return DatabaseHandler::GetAllBind($sql,$params);
		
	}
	
	
	public static function GetProductDetails($productId){
		
		//Build sql query to get product details 
		$sql="SELECT product_id,name,description,price,
		     discounted_price,image,image_2 FROM  product where 
		     product_id =:product_id";
		
		
		$params =array(':product_id'=>$productId);
		
		//Execute query and return results 
		return DatabaseHandler::GetRow($sql,$params);
		
		
	}
	
	public static function GetProductLocations($productId){
		
		//Select the product locations 
		$sql = "SELECT c.category_id,c.department_id ,c.name AS category_name,(
		       SELECT name FROM department
		       WHERE department_id = c.department_id
			   ) AS department_name FROM category c
		       WHERE c.category_id =(SELECT category_id
               FROM product_category WHERE product_id =:product_id )";
		
		$params =array(':product_id' => $productId );
		
		//Execute query and return results
		return DatabaseHandler::GetAll($sql,$params);
		 
	}
		
	
	public static function GetProductAttributes($productId){
		
		//select the attribute name and value for a particular product 
		$sql ="SELECT a.name AS attribute_name, av.attribute_value_id ,av.value AS attribute_value
               FROM attribute_value av
               INNER JOIN attribute a ON av.attribute_id = a.attribute_id
               WHERE av.attribute_value_id
               IN (SELECT attribute_value_id
               FROM product_attribute
               WHERE product_id =:product_id
               )ORDER BY a.name ";
		
		$params =array(':product_id' => $productId );
		
		//Execute query and return results
		return DatabaseHandler::GetAll($sql,$params);
		
	} 
	
	//Retrive department Name 
	public static function GetDepartmentName($departmentId){
		
		//Build department query 
		$sql ="select name from department where department_id=:department_id ";
		
		//Build the parameter array 
		$params =array(':department_id' => $departmentId );
		
		//Execute query and return results
		return DatabaseHandler::GetOne($sql,$params);
		
	}
	
	//Retrive Category  Name 
	public static function GetCategoryName($categoryId){
		
		//Build department query 
		$sql ="select name from category where category_id=:category_id ";
		
		//Build the parameter array 
		$params =array(':category_id' => $categoryId );
		
		//Execute query and return results
		return DatabaseHandler::GetOne($sql,$params);
		
	}
	
	
   //Retrive Product  Name 
	public static function GetProductName($productId){
		
		//Build product query 
		$sql ="select name from product where product_id=:product_id ";
		
		//Build the parameter array 
		$params =array(':product_id' => $productId );
		
		//Execute query and return results
		return DatabaseHandler::GetOne($sql,$params);
		
	}
	
	// Search the catalog
	public static function Search($searchString,$allWords,$pageNo,&$rHowManyPages)
	{
		//The search result will be an arrya of this form 
		$search_result =array('accepted_words'=>array(),'ignored_words'=>array(),'products'=>array());
		
		//Return void if search string is void 
		if(empty($searchString))
		  return $search_result;
		  
		//Search string delimiters 
		$delimiters=',.;';

		/* On the first call to strtok you supply the whole 
		 * search string and the delimiters.
		 * it return the first word of the strig */
		$word =strtok($searchString,$delimiters);

		//Parse the string word by word until there is no more words 
		
		while($word)
		{
			//short words are added to ignored word list from serch result 
			if(strlen($word) < FT_MIN_WORD_LEN)
			$search_result['ignored_words'][] =$word;
			else 
			$search_result['accepted_words'][] =$word;
			
			//Get next word of the search string 
			
			$word =strtok($delimiters);
			
          }
          
          // if there are not any accepted words return search result 
          
          if(count($search_result['accepted_words']) == 0)
          return $search_result;
          
          //Build search strings from accepted words list 
          $search_string='';
          
          //check all words is 'on' then append + to each word 
          if(strcmp($allWords,"on") == 0)
          $search_string = implode(" +",$search_result['accepted_words']);
          else 
          $search_string = implode(" ",$search_result['accepted_words']);
          
          // count the no of search result 
          if(strcmp($allWords,"on") == 0)
          $sql = "select count(*) 
                from  product where 
                MATCH(name,description) AGAINST (:search_string IN BOOLEAN MODE)" ;
          else 
          $sql = "select count(*) 
                from  product where 
                MATCH(name,description) AGAINST (:search_string)" ;
          
          
          $params =array(':search_string' =>$search_string);
          
          // calculate the no of page require to display to display
          $rHowManyPages =Catalog::HowManyPages($sql, $params) ;
          // calculate the start item 
          $start_item = ($pageNo-1) * PRODUCTS_PER_PAGE ;
          
          $dots ='...';
          //Retrive  the list of matched products 
          if(strcmp($allWords,"on") == 0)
            $sql = "SELECT product_id,name,
		         IF(LENGTH(description) <= :short_product_description_length,
		         description ,CONCAT(LEFT(description ,:short_product_description_length),
		         :dots )) AS description ,price,discounted_price,
		         thumbnail FROM  product WHERE 
		         MATCH (name,description)AGAINST(:search_string IN BOOLEAN MODE)
		         ORDER BY MATCH (name,description) AGAINST(:search_string IN BOOLEAN MODE) DESC 
		         LIMIT :start_item,:products_per_page" ;
          else 
            $sql = "SELECT product_id,name,
		         IF(LENGTH(description) <= :short_product_description_length,
		         description ,CONCAT(LEFT(description ,:short_product_description_length),
		         :dots )) AS description ,price,discounted_price,
		         thumbnail FROM  product WHERE 
		         MATCH (name,description)AGAINST(:search_string )
		         ORDER BY MATCH (name,description) AGAINST(:search_string) DESC 
		         LIMIT :start_item,:products_per_page " ;
          
          
          
         $params = array (
        ':short_product_description_length' =>array(SHORT_PRODUCT_DESCRIPTION_LENGTH,PDO::PARAM_INT),
        ':dots'=>array($dots,PDO::PARAM_STR),
        ':search_string' =>array($search_string,PDO::PARAM_STR),
        ':start_item' =>array($start_item,PDO::PARAM_INT),
        ':products_per_page' => array(PRODUCTS_PER_PAGE,PDO::PARAM_INT)
        );
          
        //Execute the query 
        $search_result['products'] = DatabaseHandler::GetAllBind($sql,$params);
       
        
        //Return results 
        return $search_result;  
          
		 
	 } 
	 
	 public static function GetDepartmentsWithDescriptions(){
	 	
	 	//Build the Sql query
	 	$sql = "select department_id,name,description
	 	from department 
	 	order by department_id ";
	 	
	 	//Execute the query and return results 
	 	return DatabaseHandler::GetAll($sql);
	 }
	 
	 public static function AddDepartment($department,$departmentDescription)
	 {
	 	//Build the sql query 
	 	$sql ="insert into department(name,description)
	 	values(:department_name,:department_description)";
	 	
	 	//Build the parameters array 
	 	$params =array(':department_name' =>$department , 
	 	               ':department_description' =>$departmentDescription);
	 	
	 	//Execute query 
	 	DatabaseHandler::Execute($sql,$params);
	 	
	 }
	 
	 public static function UpdateDepartment($departmentId,$departmentName,$departmentDescrition)
	 {
	 	//Build the Sql Query
	 	$sql ="update department 
	 	set name=:department_name,description=:department_description 
	 	where department_id =:department_id ";
	 	
	 	//Build the parameters array 
	 	$params =array(':department_id' =>$departmentId,':department_name'=>$departmentName,
	 	               ':department_description' =>$departmentDescrition );
	 	
	 	//Execute the query 
	 	DatabaseHandler::Execute($sql,$params);
	 	
	 }
	 
	 
	 public static function DeleteDepartment($departmentId)
	 {
      //Build the sql query 
      $sql = "select count(*) from category
              where department_id=:department_id";
      
      //Build the parameters array 
      $params =array(':department_id' => $departmentId);
      
      
      //Execute the query and return the results 
      $categoryRowsCount=DatabaseHandler::GetOne($sql,$params);
      
      if($categoryRowsCount == 0){
      	//Build the sql query 
      	$sql1="delete from department where department_id =:department_id " ;
      	
      	//Build the parameters array 
      	$params =array(':department_id' => $departmentId);
      	
      	//Execute the query and return the results 
        $delete=DatabaseHandler::Execute($sql1,$params);
       
        if($delete >0)
         return 1;  
      }else{
      	 return -1;
      }
      
      }
      
      //Get categories in department 
      public static function GetDepartmentCategories($departmentId)
      {
      	//Build the sql query 
      	$sql ="select category_id,name,description from category 
      	where department_id =:department_id  order by category_id";
      	
      	// Build the parameter array 
      	$params =array(':department_id' => $departmentId);
      	
      	//Execute the query and return results 
      	return DatabaseHandler::GetAll($sql,$params);
      }

     //Adds a category 
     public static function AddCategory($departmentId,$categoryName,$categoryDescription)
     {
     	//$sql =""
     	$sql ="insert into category(department_id,name,description)
	 	values(:department_id,:category_name,:category_description)";
     	
     	//Build parameter array 
     	$params= array(':department_id' => $departmentId,
     	               ':category_name' => $categoryName,
     	               ':category_description' => $categoryDescription
     	               );

     	//Execute the query               
        DatabaseHandler::Execute($sql,$params);
     	               
     }
     
     // Updates  a category 
     public static function UpdateCategory($categoryId,$categoryName,$categoryDescription)
     {
     	//Build the sql query 
     	$sql ="update category set name =:category_name,description =:category_description 
     	       where category_id =:category_id ";

     	$params =array(':category_id' =>$categoryId,
     	               ':category_name' =>$categoryName,
     	               ':category_description' =>$categoryDescription);
     
     	
     	//Execute the query 
        DatabaseHandler::Execute($sql,$params);
     	
     }
	
     //Deletes  a category 
    public static function DeleteCategory($categoryId)
	 {
      //Build the sql query 
      $sql = "SELECT count(*) FROM product p
             INNER JOIN product_category pc 
             ON p.product_id =pc.product_id
             WHERE pc.category_id=:category_id ";
      
      //Build the parameters array 
      $params =array(':category_id' => $categoryId);
      
      
      //Execute the query and return the results 
      $productsRowsCount=DatabaseHandler::GetOne($sql,$params);
      
      if($productsRowsCount == 0){
      	//Build the sql query 
      	$sql1="delete from category where category_id =:category_id " ;
      	
      	//Build the parameters array 
      	$params =array(':category_id' => $categoryId);
      	
      	//Execute the query and return the results 
        $delete=DatabaseHandler::Execute($sql1,$params);
       
        if($delete > 0)
         return  1;  
      }else{
      	 return -1;
      }
      
      }
	 
	 
      
      
	
}

